package net.assassin8.bautista.stalker;

import java.io.File;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.Editable;
import android.util.Log;

public class StalkerProvider {

	// Database variables
	private static final String DATABASE_NAME = "stalker";
	private static final int DATABASE_VERSION = 1;
	private static final String TAG = "StalkerProvider";

	// Malls table variables
	private static final String TABLE_MALLS = "malls";
	public static final String KEY_MALL_ROW_ID = "id";
	public static final String KEY_MALL_NAME = "name";
	public static final String KEY_MALL_ADDRESS = "address";
	public static final String KEY_MALL_DESCRIPTION = "description";
	public static final String KEY_MALL_FLOORS = "number_of_floors";

	// Floor Plans table variables
	private static final String TABLE_FLOOR_PLANS = "floor_plans";
	public static final String KEY_FP_ROW_ID = "id";
	public static final String KEY_FP_MALL_ID = "mall_id";
	public static final String KEY_FP_FLOOR_NUMBER = "floor_number";
	public static final String KEY_FP_WIDTH = "width";
	public static final String KEY_FP_HEIGHT = "height";
	public static final String KEY_FP_DESCRIPTION = "description";
	public static final String KEY_FP_IMG_FILE = "image_filename";
	public static final String KEY_FP_IMG_THUMB = "image_thumbnail";
	public static final String KEY_FP_PXM = "pixels_per_meter";

	// Path Nodes table variables
	private static final String TABLE_PATH_NODES = "path_nodes";
	public static final String KEY_PATH_ROW_ID = "id";
	public static final String KEY_PATH_FLOOR_ID = "floor_plan_id";
	public static final String KEY_PATH_X = "x";
	public static final String KEY_PATH_Y = "y";

	/*
	 * // Shop Nodes table variables private static final String
	 * TABLE_SHOP_NODES = "shop_nodes"; public static final String
	 * KEY_SHOP_ROW_ID = "id"; public static final String KEY_SHOP_FLOOR_ID =
	 * "floor_plan_id"; public static final String KEY_SHOP_CATEGORY_ID =
	 * "shop_category_id"; public static final String KEY_SHOP_X = "x"; public
	 * static final String KEY_SHOP_Y = "y"; public static final String
	 * KEY_SHOP_NAME = "name"; public static final String KEY_SHOP_DESCRIPTION =
	 * "description";
	 */

	private static final String TABLE_SHOPS = "shops";
	public static final String KEY_SHOP_ROW_ID = "id";
	public static final String KEY_SHOP_FLOOR_ID = "floor_plan_id";
	public static final String KEY_SHOP_CATEGORY_ID = "shop_category_id";
	public static final String KEY_SHOP_START_X = "start_x";
	public static final String KEY_SHOP_START_Y = "start_y";
	public static final String KEY_SHOP_END_X = "end_x";
	public static final String KEY_SHOP_END_Y = "end_y";
	public static final String KEY_SHOP_NAME = "name";

	// Subjects table variables
	private static final String TABLE_SUBJECTS = "subjects";
	public static final String KEY_SUBJ_ROW_ID = "id";
	public static final String KEY_SUBJ_MALL_ID = "mall_id";
	public static final String KEY_SUBJ_FIRST_NAME = "first_name";
	public static final String KEY_SUBJ_LAST_NAME = "last_name";
	public static final String KEY_SUBJ_GENDER = "gender";
	public static final String KEY_SUBJ_AGE = "age";

	// Shop category table variables
	private static final String TABLE_SHOP_CATEGORIES = "shop_categories";
	public static final String KEY_SC_ROW_ID = "id";
	public static final String KEY_SC_NAME = "name";
	public static final String KEY_SC_DESCRIPTION = "description";

	// Sessions table variables
	private static final String TABLE_SESSIONS = "sessions";
	public static final String KEY_SESS_ROW_ID = "id";
	public static final String KEY_SESS_SUBJECT_ID = "subject_id";

	// Old Session trajectory table variables
	/*
	 * private static final String TABLE_SESSIONS_TRAJECTORY =
	 * "sessions_trajectory"; public static final String KEY_SESS_TRAJ_ROW_ID =
	 * "id"; public static final String KEY_SESS_TRAJ_TIME = "time"; public
	 * static final String KEY_SESS_TRAJ_SESS_ID = "session_id"; public static
	 * final String KEY_SESS_TRAJ_PATH_NODE_ID = "path_node_id"; public static
	 * final String KEY_SESS_TRAJ_SHOP_NODE_ID = "shop_node_id";
	 */

	// Session trajectory table varibles
	private static final String TABLE_SESSIONS_TRAJECTORY = "sessions_trajectory";
	public static final String KEY_SESS_TRAJ_ROW_ID = "id";
	public static final String KEY_SESS_TRAJ_TIME = "time";
	public static final String KEY_SESS_TRAJ_SESS_ID = "session_id";
	public static final String KEY_SESS_TRAJ_PATH_X = "path_x";
	public static final String KEY_SESS_TRAJ_PATH_Y = "path_y";
	public static final String KEY_SESS_TRAJ_SHOP_ID = "shop_id";

	private static final String CREATE_TABLE_MALLS = "create table "
			+ TABLE_MALLS + " (" + KEY_MALL_ROW_ID
			+ " integer primary key autoincrement, " + KEY_MALL_NAME
			+ " text, " + KEY_MALL_ADDRESS + " text, " + KEY_MALL_DESCRIPTION
			+ " text, " + KEY_MALL_FLOORS + " int);";

	private static final String CREATE_TABLE_FLOOR_PLANS = "create table "
			+ TABLE_FLOOR_PLANS + " (" + KEY_FP_ROW_ID
			+ " integer primary key autoincrement, " + KEY_FP_MALL_ID
			+ " integer, " + KEY_FP_FLOOR_NUMBER + " integer, " + KEY_FP_WIDTH
			+ " integer, " + KEY_FP_HEIGHT + " integer, " + KEY_FP_DESCRIPTION
			+ " text, " + KEY_FP_IMG_FILE + " text, " + KEY_FP_IMG_THUMB
			+ " text, " + KEY_FP_PXM + " float);";

	private static final String CREATE_TABLE_PATH_NODES = "create table "
			+ TABLE_PATH_NODES + " (" + KEY_PATH_ROW_ID
			+ " integer primary key autoincrement, " + KEY_PATH_FLOOR_ID
			+ " integer not null, " + KEY_PATH_X + " integer not null, "
			+ KEY_PATH_Y + " integer not null);";

	/*
	 * private static final String CREATE_TABLE_SHOP_NODES = "create table " +
	 * TABLE_SHOP_NODES + " (" + KEY_SHOP_ROW_ID +
	 * " integer primary key autoincrement, " + KEY_SHOP_FLOOR_ID +
	 * " integer not null, " + KEY_SHOP_CATEGORY_ID + " integer, " + KEY_SHOP_X
	 * + " integer not null, " + KEY_SHOP_Y + " integer not null, " +
	 * KEY_SHOP_NAME + " text, " + KEY_SHOP_DESCRIPTION + " text);";
	 */

	private static final String CREATE_TABLE_SHOPS = "create table "
			+ TABLE_SHOPS + " (" + KEY_SHOP_ROW_ID
			+ " integer primary key autoincrement, " + KEY_SHOP_FLOOR_ID
			+ " integer not null, " + KEY_SHOP_CATEGORY_ID + " integer, "
			+ KEY_SHOP_START_X + " integer not null, " + KEY_SHOP_START_Y
			+ " integer not null, " + KEY_SHOP_END_X + " integer not null, "
			+ KEY_SHOP_END_Y + " integer not null, " + KEY_SHOP_NAME
			+ " text);";

	private static final String CREATE_TABLE_SUBJECTS = "create table "
			+ TABLE_SUBJECTS + " (" + KEY_SUBJ_ROW_ID
			+ " integer primary key autoincrement, " + KEY_SUBJ_MALL_ID
			+ " integer not null, " + KEY_SUBJ_FIRST_NAME + " text, "
			+ KEY_SUBJ_LAST_NAME + " text, " + KEY_SUBJ_GENDER + " text, "
			+ KEY_SUBJ_AGE + " integer);";

	private static final String CREATE_TABLE_SHOP_CATEGORIES = "create table "
			+ TABLE_SHOP_CATEGORIES + " (" + KEY_SC_ROW_ID
			+ " integer primary key autoincrement, " + KEY_SC_NAME
			+ " text not null, " + KEY_SC_DESCRIPTION + " text);";

	private static final String CREATE_TABLE_SESSIONS = "create table "
			+ TABLE_SESSIONS + " (" + KEY_SESS_ROW_ID
			+ " integer primary key autoincrement, " + KEY_SESS_SUBJECT_ID
			+ " integer not null)";

	private static final String CREATE_TABLE_SESSIONS_TRAJECTORY = "create table "
			+ TABLE_SESSIONS_TRAJECTORY
			+ " ("
			+ KEY_SESS_TRAJ_ROW_ID
			+ " integer primary key autoincrement, "
			+ KEY_SESS_TRAJ_TIME
			+ " numeric not null, "
			+ KEY_SESS_TRAJ_SESS_ID
			+ " numeric not null, "
			+ KEY_SESS_TRAJ_PATH_X
			+ " numeric not null, "
			+ KEY_SESS_TRAJ_PATH_Y
			+ " numeric not null," + KEY_SESS_TRAJ_SHOP_ID + " numeric)";

	@SuppressWarnings("unused")
	private final Context context;

	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;

	public StalkerProvider(Context ctx) {
		this.context = ctx;
		DBHelper = new DatabaseHelper(ctx);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_TABLE_MALLS);
			db.execSQL(CREATE_TABLE_FLOOR_PLANS);
			db.execSQL(CREATE_TABLE_PATH_NODES);
			// db.execSQL(CREATE_TABLE_SHOP_NODES);
			db.execSQL(CREATE_TABLE_SHOPS);
			db.execSQL(CREATE_TABLE_SUBJECTS);
			db.execSQL(CREATE_TABLE_SHOP_CATEGORIES);
			db.execSQL(CREATE_TABLE_SESSIONS);
			db.execSQL(CREATE_TABLE_SESSIONS_TRAJECTORY);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_MALLS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_FLOOR_PLANS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_PATH_NODES);
			// db.execSQL("DROP TABLE IF EXISTS " + TABLE_SHOP_NODES);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_SHOPS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_SUBJECTS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_SHOP_CATEGORIES);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_SESSIONS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_SESSIONS_TRAJECTORY);
			onCreate(db);
		}
	}

	// ---opens the database---
	public StalkerProvider open() throws SQLException {
		db = DBHelper.getWritableDatabase();
		return this;
	}

	// ---closes the database---
	public void close() {
		DBHelper.close();
	}

	// ---insert a mall---
	public long insertMall(long id, String name, String address,
			String mDescription, String floorNumber2) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_MALL_ROW_ID, id);
		initialValues.put(KEY_MALL_NAME, name);
		initialValues.put(KEY_MALL_ADDRESS, address);
		initialValues.put(KEY_MALL_DESCRIPTION, mDescription);
		initialValues.put(KEY_MALL_FLOORS, floorNumber2);
		Log.i(TAG, "Inserted mall with ID number: " + id);
		return db.insert(TABLE_MALLS, null, initialValues);
	}

	public long insertFloorPlan(long id, long mallId, String floorNumber,
			String width, String height, String description,
			String image_filename, String image_thumb, String pxm) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_FP_ROW_ID, id);
		initialValues.put(KEY_FP_MALL_ID, mallId);
		initialValues.put(KEY_FP_FLOOR_NUMBER, floorNumber);
		initialValues.put(KEY_FP_WIDTH, width);
		initialValues.put(KEY_FP_HEIGHT, height);
		initialValues.put(KEY_FP_DESCRIPTION, description);
		initialValues.put(KEY_FP_IMG_FILE, image_filename);
		initialValues.put(KEY_FP_IMG_THUMB, image_thumb);
		initialValues.put(KEY_FP_PXM, pxm);

		long insertedId = db.insert(TABLE_FLOOR_PLANS, null, initialValues);
		Log.i(TAG, "Inserted floor plan with ID number: " + insertedId);
		return insertedId;
	}

	// ---insert a path node---
	public long insertPathNode(long id, long floor_plan_id, String x, String y) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_PATH_ROW_ID, id);
		initialValues.put(KEY_PATH_FLOOR_ID, floor_plan_id);
		initialValues.put(KEY_PATH_X, x);
		initialValues.put(KEY_PATH_Y, y);
		Log.i(TAG, "Inserted path node with ID number: " + id);
		return db.insert(TABLE_PATH_NODES, null, initialValues);
	}

	/*
	 * // ---insert a shop node--- public long insertShopNode(long id, long
	 * floor_plan_id, String shopCategoryId, String x, String y, String name,
	 * String description) { ContentValues initialValues = new ContentValues();
	 * initialValues.put(KEY_SHOP_ROW_ID, id);
	 * initialValues.put(KEY_SHOP_FLOOR_ID, floor_plan_id);
	 * initialValues.put(KEY_SHOP_CATEGORY_ID, shopCategoryId);
	 * initialValues.put(KEY_SHOP_X, x); initialValues.put(KEY_SHOP_Y, y);
	 * initialValues.put(KEY_SHOP_NAME, name);
	 * initialValues.put(KEY_SHOP_DESCRIPTION, description); Log.i(TAG,
	 * "Inserted shop node with ID number: " + id); return
	 * db.insert(TABLE_SHOP_NODES, null, initialValues); }
	 */

	// ---insert a subject---
	public long insertSubject(long id, long mall_id, String first_name,
			String last_name, String gender, int age) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SUBJ_ROW_ID, id);
		initialValues.put(KEY_SUBJ_MALL_ID, mall_id);
		initialValues.put(KEY_SUBJ_FIRST_NAME, first_name);
		initialValues.put(KEY_SUBJ_LAST_NAME, last_name);
		initialValues.put(KEY_SUBJ_GENDER, gender);
		initialValues.put(KEY_SUBJ_AGE, age);
		Log.i(TAG, "Inserted subject with ID number: " + id);
		return db.insert(TABLE_SUBJECTS, null, initialValues);
	}

	// ---get all malls---
	public Cursor getAllMalls() {
		return db.query(TABLE_MALLS, new String[] { KEY_MALL_ROW_ID,
				KEY_MALL_NAME, KEY_MALL_ADDRESS, KEY_MALL_DESCRIPTION,
				KEY_MALL_FLOORS }, null, null, null, null, null);
	}

	public void deletePreviousFloorPlans(long id) {

		// Also delete floor plan images
		// So we get the floor plan file names first
		Cursor query = db.query(TABLE_FLOOR_PLANS, new String[] {
				KEY_FP_IMG_FILE, KEY_FP_IMG_THUMB }, "id = " + id, null, null,
				null, null);
		if (query.moveToFirst()) {
			File f = new File(Stalker.PATH + query.getString(0));
			f.delete();
			File f2 = new File(Stalker.PATH + query.getString(1));
			f2.delete();
		}
		query.close();
		db.execSQL("DELETE FROM " + TABLE_FLOOR_PLANS + " WHERE id = " + id);
		db.execSQL("DELETE FROM " + TABLE_PATH_NODES
				+ " WHERE floor_plan_id = " + id);
		// db.execSQL("DELETE FROM " + TABLE_SHOP_NODES
		// + " WHERE floor_plan_id = " + id);
		Log.i(TAG, "Deleted floor plan from database: " + id);
	}

	public void deletePreviousMall(long mallId) {
		db.execSQL("DELETE FROM " + TABLE_MALLS + " WHERE id = " + mallId);
		Log.i(TAG, "Deleted mall from database: " + mallId);
	}

	public long insertShopCategory(long id, String name, String description) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SC_ROW_ID, id);
		initialValues.put(KEY_SC_NAME, name);
		initialValues.put(KEY_SC_DESCRIPTION, description);
		Log.i(TAG, "Inserted shop category with ID: " + id);
		return db.insert(TABLE_SHOP_CATEGORIES, null, initialValues);

	}

	public void deletePreviousShopCategories() {
		db.execSQL("DELETE FROM " + TABLE_SHOP_CATEGORIES);

	}

	public Cursor getFloorPlan(long mallId, int floorNumber) {

		Cursor query = db.query(TABLE_FLOOR_PLANS, new String[] {
				KEY_FP_ROW_ID, KEY_FP_IMG_FILE, KEY_FP_WIDTH, KEY_FP_HEIGHT },
				KEY_FP_MALL_ID + " = " + mallId + " AND " + KEY_FP_FLOOR_NUMBER
						+ " = " + floorNumber, null, null, null, null);
		return query;

	}

	public Cursor getPathNodes(long floorPlanId) {
		Cursor query = db.query(TABLE_PATH_NODES, new String[] {
				KEY_PATH_ROW_ID, KEY_PATH_X, KEY_PATH_Y }, KEY_PATH_FLOOR_ID
				+ " = " + floorPlanId, null, null, null, null);
		return query;
	}

	/*
	 * public Cursor getShopNodes(long floorPlanId) { Cursor query = db
	 * .query(TABLE_SHOP_NODES, new String[] { KEY_SHOP_ROW_ID, KEY_SHOP_X,
	 * KEY_SHOP_Y, KEY_SHOP_NAME }, KEY_PATH_FLOOR_ID + " = " + floorPlanId,
	 * null, null, null, null); return query; }
	 */

	public long insertSubject(long mallId, Editable firstName,
			Editable lastName, String gender, Editable age) {
		String fName = firstName.toString();
		String lName = lastName.toString();
		String a = age.toString();

		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SUBJ_MALL_ID, mallId);
		initialValues.put(KEY_SUBJ_FIRST_NAME, fName);
		initialValues.put(KEY_SUBJ_LAST_NAME, lName);
		initialValues.put(KEY_SUBJ_GENDER, gender);
		initialValues.put(KEY_SUBJ_AGE, a);
		long id = db.insert(TABLE_SUBJECTS, null, initialValues);
		Log.i(TAG, "Inserted subject with ID number: " + id);
		return id;
	}

	public long insertSession(long subjectId) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SESS_SUBJECT_ID, subjectId);
		long id = db.insert(TABLE_SESSIONS, null, initialValues);
		Log.i(TAG, "Inserted session with ID number: " + id);
		return id;
	}

	public long insertSessionTrajectory(long sessionId, long pathX, long pathY) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SESS_TRAJ_SESS_ID, sessionId);
		initialValues.put(KEY_SESS_TRAJ_TIME, now());
		initialValues.put(KEY_SESS_TRAJ_PATH_X, pathX);
		initialValues.put(KEY_SESS_TRAJ_PATH_Y, pathY);
		long id = db.insert(TABLE_SESSIONS_TRAJECTORY, null, initialValues);
		Log.i(TAG, "Inserted session trajectory with ID number: " + id);
		return id;
	}

	// ---Get the current time---
	public long now() {
		long epoch = System.currentTimeMillis() / 1000;
		return epoch;
	}

	public Cursor getShops(long floorPlanId) {
		return db.query(TABLE_SHOPS, new String[] { KEY_SHOP_ROW_ID,
				KEY_SHOP_NAME, KEY_SHOP_FLOOR_ID, KEY_SHOP_CATEGORY_ID,
				KEY_SHOP_START_X, KEY_SHOP_START_Y, KEY_SHOP_END_X,
				KEY_SHOP_END_Y }, KEY_SHOP_FLOOR_ID + " = " + floorPlanId,
				null, null, null, null);
	}

	public void insertShop(long id, long floorPlanId, String name,
			String shopCategoryId, String startX, String startY, String endX,
			String endY) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SHOP_ROW_ID, id);
		initialValues.put(KEY_SHOP_FLOOR_ID, floorPlanId);
		initialValues.put(KEY_SHOP_NAME, name);
		initialValues.put(KEY_SHOP_CATEGORY_ID, shopCategoryId);
		initialValues.put(KEY_SHOP_START_X, startX);
		initialValues.put(KEY_SHOP_START_Y, startY);
		initialValues.put(KEY_SHOP_END_X, endX);
		initialValues.put(KEY_SHOP_END_Y, endY);
		long thisId = db.insert(TABLE_SHOPS, null, initialValues);
		Log.i(TAG, "Inserted shop with ID number: " + thisId);

	}

	public Cursor getMall(long mallId) {
		return db.query(TABLE_MALLS, new String[] { KEY_MALL_NAME,
				KEY_MALL_FLOORS, KEY_MALL_ADDRESS, KEY_MALL_DESCRIPTION },
				KEY_MALL_ROW_ID + " = " + mallId, null, null, null, null);
	}

	public Cursor getAllSubjects() {
		return db.query(TABLE_SUBJECTS, new String[] { KEY_SUBJ_ROW_ID,
				KEY_SUBJ_MALL_ID, KEY_SUBJ_FIRST_NAME, KEY_SUBJ_LAST_NAME,
				KEY_SUBJ_GENDER, KEY_SUBJ_AGE }, null, null, null, null, null);
	}

	public Cursor getAllSessions() {
		return db.query(TABLE_SESSIONS, new String[] { KEY_SESS_ROW_ID,
				KEY_SESS_SUBJECT_ID }, null, null, null, null, null);
	}

	public Cursor getAllSessionsTrajectory() {
		return db.query(TABLE_SESSIONS_TRAJECTORY, new String[] {
				KEY_SESS_TRAJ_ROW_ID, KEY_SESS_TRAJ_TIME,
				KEY_SESS_TRAJ_SESS_ID, KEY_SESS_TRAJ_PATH_X,
				KEY_SESS_TRAJ_PATH_Y, KEY_SESS_TRAJ_SHOP_ID }, null, null,
				null, null, null);
	}

	public void emptySubjectsTable() {
		db.execSQL("DELETE FROM " + TABLE_SUBJECTS + ";");
		db.execSQL("VACUUM");
	}

	public void emptySessionsTable() {
		db.execSQL("DELETE FROM " + TABLE_SESSIONS + ";");
		db.execSQL("VACUUM");
	}

	public void emptySessionsTrajectoryTable() {
		db.execSQL("DELETE FROM " + TABLE_SESSIONS_TRAJECTORY + ";");
		db.execSQL("VACUUM");
	}
}
